<?php

class DML {
	const DSN = "mysql:host=localhost;dbname=qruiz";
	const DB_USERNAME = "";
	const DB_PASSWORD = "";

	protected static $instance;
	protected $pdo;

	protected function __construct() {
		$this->pdo = new PDO(self::DSN, self::DB_USERNAME, self::DB_PASSWORD, array(
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
			, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
		));
	}

	public static function getInstance() {
		if (empty(self::$instance)) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	// dirty hack for achievements, use sparingly
	public function getPDO() {
		return $this->pdo;
	}

	/**
	 * Magic __call method
	 *
	 * This method handles various calls that match the same signature. It
	 * converts calls to getXXXByID to getEntityByID and converts calls to
	 * saveXXX to saveEntity. If special action is required for some data object,
	 * impement the method with its proper name
	 *
	 * @return mixed
	 */
	public function __call($name, $args) {
		if (preg_match('/^get(.+)ByID$/', $name, $matches)) {
			// prepend entity name to argument list
			$args = array_merge(array($matches[1]), $args);
			return call_user_func_array(array($this, "getEntityByID"), $args);
		} else if (preg_match('/^save(.+)$/', $name, $matches)) {
			return call_user_func_array(array($this, "saveEntity"), $args);
		}
		user_error("Call to undefined function DML::" . $name, E_USER_ERROR);
	}

	/**
	 * Get a generic entity
	 *
	 * This method executes
	 * "SELECT * FROM <var>$entity</var> WHERE id=<var>$id</var>" against the
	 * database and returns an object of type <var>$entity</var> constructed
	 * from the result
	 *
	 * @param string $entity
	 * @param int $id
	 *
	 * @return mixed
	 */
	public function getEntityByID($entity, $id) {
		$sql = "SELECT * FROM " . $entity . " WHERE id=?";	// this fails if table $entity does not exist
		$stmt = $this->pdo->prepare($sql);
		$stmt->execute(array($id));
		if ($row = $stmt->fetch()) {
			$result = new $entity($row);	// this fails if class $entity is not defined
			return $result;
		} else {
			return NULL;
		}
	}

	/**
	 * Get all entities of a specific type
	 *
	 * @param string $entity Any of the entities defined
	 *
	 * @return array
	 */	
	public function getAll($entity) {
		$sql = "SELECT * FROM " . $entity;
		$stmt = $this->pdo->prepare($sql);
		$stmt->execute();
		$result = array();
		while ($row = $stmt->fetch()) {	// collect results
			$result[] = new $entity($row);
		}
		
		return $result;
	}

	/**
	 * Save an entity's information to the DB
	 *
	 * If $entity's id is not set (or evaluates to false), an INSERT
	 * operation is executed, otherwise an UPDATE is performed. In the former
	 * case, $entity will be altered to incorporate the newly generated id.
	 */
	public function saveEntity(DataObject $entity) {
		$reflector = new ReflectionObject($entity);
		$entityName = $reflector->getName();
		$entityAsArray = array();

		foreach ($entity as $key => $value) {
			if (!$entity->isIndirectProperty($key) && !$entity->isCustomProperty($key)) {
				$entityAsArray[$key] = $value;
			}
		}
		if (empty($entity->id)) {
			unset($entityAsArray["id"]);
			$sql = "INSERT INTO " . $entityName . " (";
			$sql .= implode(",", array_keys($entityAsArray));
			$sql .= ") VALUES (:";
			$sql .= implode(",:", array_keys($entityAsArray));
			$sql .= ")";
		} else {
			// build SQL for update
			$sql = "UPDATE " . $entityName . " SET ";
			$keyValuePairs = array();
			foreach ($entityAsArray as $key => $value) {
				if ($key != "id") {
					$keyValuePairs[] = sprintf("%s=:%s", $key, $key);
				}
			}
			$sql .= implode(", ", $keyValuePairs);
			$sql .= " WHERE id=:id";
		}
		$stmt = $this->pdo->prepare($sql);
		foreach ($entityAsArray as $key => $value) {
			if ($key != "id") {
				$stmt->bindValue(":" . $key, $value);
			}
		}
		if (!empty($entity->id)) {
			$stmt->bindValue(":id", $id);
		}
		$stmt->execute();
		if (empty($entity->id)) {
			$entity->id = $this->pdo->lastInsertId();
		}
	}

 	/**
	 * Remove an entity
	 */
	public function remove(DataObject $entity) {
		$reflector = new ReflectionObject($entity);
		$entityName = $reflector->getName();
		$sql = sprintf("DELETE FROM %s WHERE id=?", $entityName);
		$stmt = $this->pdo->prepare($sql);
		$stmt->execute(array($entity->id));
	}

	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	// look up a user by its e-mail address
	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	public function getUserByEmail($email) {
		$sql = "SELECT * FROM User WHERE email=:email";
		$stmt = $this->pdo->prepare($sql);
		$stmt->bindValue(":email", $email);
		$stmt->execute();
		if ($row = $stmt->fetch()) {
			return new User($row);
		}
		return NULL;
	}

	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	// save an answer a user has given
	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	public function saveUserAnswer(User $user, PossibleAnswer $answer) {
		$sql = "INSERT INTO UserAnswer (userID, possibleAnswerID) VALUES (:userID, :possibleAnswerID)";
		$stmt = $this->pdo->prepare($sql);
		$stmt->bindValue(":userID", $user->id);
		$stmt->bindValue(":possibleAnswerID", $answer->id);
		$stmt->execute();
	}

	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	// get the answers a user has given
	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	public function getAnswersGiven(User $user) {
		$sql = "SELECT * FROM PossibleAnswer WHERE id IN (SELECT possibleAnswerID FROM UserAnswer WHERE userID=" . $user->id . ")";
		$stmt = $this->pdo->prepare($sql);
		$stmt->execute();
		$result = array();
		while ($row = $stmt->fetch()) {
			$result[] = new PossibleAnswer($row);
		}
		return $result;
	}

	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	// save an answer a user has given
	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	public function saveUserAchievement(User $user, Achievement $achievement) {
		$sql = "INSERT INTO UserAchievement (userID, achievementID) VALUES (:userID, :achievementID)";
		$stmt = $this->pdo->prepare($sql);
		$stmt->bindValue(":userID", $user->id);
		$stmt->bindValue(":achievementID", $achievement->id);
		$stmt->execute();
	}

	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	// get all the achievements a user has collected
	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	public function getAchievementsCollected(User $user) {
		$sql = "SELECT * FROM Achievement WHERE id IN (SELECT achievementID FROM UserAchievement WHERE userID=" . $user->id . ")";
		$stmt = $this->pdo->prepare($sql);
		$stmt->bindValue(":userID", $user->id);
		$stmt->execute();
		$result = array();
		while ($row = $stmt->fetch()) {
			$result[] = new Achievement($row);
		}
		return $result;
	}

	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	// get achievement by description
	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	public function getAchievementByDescription($description) {
		$sql = "SELECT * FROM Achievement WHERE description=:description";
		$stmt = $this->pdo->prepare($sql);
		$stmt->bindValue(":description", $description);
		$stmt->execute();
		if ($row = $stmt->fetch()) {
			return new Achievement($row);
		}
		return NULL;
	}

	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	// get all the possible answers to a question
	// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	public function getAnswersByQuestion(Question $question) {
		$sql = "SELECT * FROM PossibleAnswer WHERE questionID=:questionID";
		$stmt = $this->pdo->prepare($sql);
		$stmt->bindValue(":questionID", $question->id);
		$stmt->execute();
		$result = array();
		while ($row = $stmt->fetch()) {
			$result[] = new PossibleAnswer($row);
		}
		return $result;
	}
}
